package pck.ivrGraph.models;

import oracle.jdbc.driver.OracleDriver;
import pck.ivrGraph.models.flows.IvrComponent;
import pck.ivrGraph.models.flows.*;
import pck.ivrGraph.models.flows.IvrFlow;
import pck.ivrGraph.models.flows.IvrMacro;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.DriverManager;
import java.util.ArrayList;


public class DBModel {
	private static final String JDBC_URL = "jdbc:oracle:thin:@10.198.8.181:1521:ICP044";
	private static final String JDBC_USER = "rbtv006";
	private static final String JDBC_PASSWORD = "rbtv006";

	private static final String T_MENUINFO_QUERY	=	"SELECT MENUNAME, MENUTYPE, PROMPTNAME, COMPONENTNAME, DESCRIPTION " +
																									"FROM T_MENUINFO " +
																									"WHERE MENUNAME LIKE ";

	private static final String T_COMPONENTINFO_QUERY	=	"SELECT COMPONENTNAME, COMPONENTPATH " +
																											"FROM T_COMPONENTINFO " +
																											"WHERE COMPONENTNAME LIKE ";

	private static final String T_IVRFLOW_QUERY	=	"SELECT KEY_RETURN, PROMPT, NEXTACTIONTYPE, NEXTMENUMACRO, INPUTVARIABLES, ISENABLE " +
																								"FROM T_IVRFLOW " +
																								"WHERE " +
																								"NEXTACTIONTYPE NOT LIKE \'macro\' AND " +
																								"MENUNAME LIKE ";

	private static final String rootFlow = "setupcall";

	private IvrFlow root = null;
	private Connection dbConnection = null;
	private ArrayList<IvrFlow> flows = new ArrayList<IvrFlow>();

	public IvrFlow getRootFlow(){
		return root;
	}

	public DBModel() throws SQLException{
		DriverManager.registerDriver(new OracleDriver());
		dbConnection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
		root = buildModel(rootFlow);
	}

	private class NextFlow{
		public String key_return = null;
		public String menuname = null;
		public String menutype = null;
		public String prompt = null;
		public String inputvariables = null;
	}

	private IvrFlow buildModel(String menuname) throws SQLException{
		IvrFlow flow = createFlow(menuname);

		ArrayList<NextFlow> ivrflow = getIvrFlow(menuname);
		for(int i = 0; i < ivrflow.size(); i++){
			IvrFlow childFlow = findBuildedFlow(ivrflow.get(i).menuname);
			if(childFlow == null){
				if(ivrflow.get(i).menutype.equals("menu")){
						childFlow = buildModel(ivrflow.get(i).menuname);
				}else{
					childFlow = new IvrMacro();
					childFlow.setName(ivrflow.get(i).menuname);
					childFlow.setDescription(ivrflow.get(i).menutype);
					((IvrMacro)childFlow).setPromptName(ivrflow.get(i).prompt);
					flows.add(childFlow);
				}
			}
			flow.addFlow(ivrflow.get(i).key_return, childFlow);
		}
		return flow;
	}

	private IvrFlow createFlow(String menuname) throws SQLException{
		Statement miStatement = dbConnection.createStatement();
		ResultSet menuinfo = miStatement.executeQuery(T_MENUINFO_QUERY + "\'" + menuname + "\'");
		menuinfo.next();
		IvrFlow result;
		if(menuinfo.getString("MENUTYPE").equals("component")){
			result = new IvrComponent();
			Statement ciStatement = dbConnection.createStatement();
			ResultSet componentinfo = ciStatement.executeQuery(T_COMPONENTINFO_QUERY + "\'" + menuinfo.getString("COMPONENTNAME") + "\'");
			componentinfo.next();
			((IvrComponent)result).setComponentName(componentinfo.getString("COMPONENTNAME"));
			((IvrComponent)result).setPath(componentinfo.getString("COMPONENTPATH"));
			ciStatement.close();
		}else{
			result = new IvrPrompt();
			((IvrPrompt)result).setPromptName(menuinfo.getString("PROMPTNAME"));
		}
		result.setDescription(menuinfo.getString("DESCRIPTION"));
		result.setName(menuinfo.getString("MENUNAME"));
		miStatement.close();
		flows.add(result);
		return result;
	}

	private ArrayList<NextFlow> getIvrFlow(String menuname) throws SQLException{
		ArrayList<NextFlow> result = new ArrayList<NextFlow>();
		Statement statement = dbConnection.createStatement();
		ResultSet ivrflow = statement.executeQuery(T_IVRFLOW_QUERY + "\'" + menuname + "\'");
		while(ivrflow.next()){
			if(ivrflow.getInt("ISENABLE") == 1){
				NextFlow nextFlow = new NextFlow();
				nextFlow.menuname = ivrflow.getString("NEXTMENUMACRO");
				nextFlow.key_return = ivrflow.getString("KEY_RETURN");
				nextFlow.menutype = ivrflow.getString("NEXTACTIONTYPE");
				nextFlow.prompt = ivrflow.getString("PROMPT");
				nextFlow.inputvariables = ivrflow.getString("INPUTVARIABLES");
				result.add(nextFlow);
			}
		}
		statement.close();
		return result; 
	}

	private IvrFlow findBuildedFlow(String menuname){
		for(int i = 0; i < flows.size(); i++){
			IvrFlow child = flows.get(i);
			if(menuname.equals(child.getName())){
				return child;
			}
		}
		return null;
	}
}
